﻿using System;
using System.Data;
using System.Data.Common;
using FirebirdSql.Data.FirebirdClient;
using ProtocolVN.Framework.Core;

namespace PL.Core
{
    /// <summary>
    /// Đối tượng làm việc với db (Trial)
    /// </summary>
    public class DABaseFb {

        public DBService SQLStmt { 
            get{
                FbCommand cmd = new FbConnection(this.ConnectionString).CreateCommand();
                cmd.CommandType = CommandType.Text;
                return new DBService(cmd);
            }
        }

        public DBService StoredProcedure
        {
            get {
                FbCommand cmd = new FbConnection(this.ConnectionString).CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                return new DBService(cmd);
            }
        }

        public DBService TableDirect
        {
            get {
                FbCommand cmd = new FbConnection(this.ConnectionString).CreateCommand();
                cmd.CommandType = CommandType.TableDirect;
                return new DBService(cmd);
            }
        }

        public  DBService PLStoredProcedure(string _StoredProcedure) {
            DBService db = StoredProcedure;
            db.StoredProcedure = _StoredProcedure;
            return db;
        }

        public DBService PLSQLStmt(string _Sql) {
            DBService db = SQLStmt;
            db.Sql = _Sql;
            return db;
        }

        public string ConnectionString { 
            get{
                return DABase.getDatabase().CreateConnection().ConnectionString;
            }
        }
    }

    /// <summary>
    /// Thao tác với db thử nghiệm thư việc từ dự án VTTP
    /// </summary>
    public class DBService
    {
        FbCommand m_cmd = null;
        IDataReader m_reader = null;

        public DBService(FbCommand cmd)
        {
            m_cmd = cmd;
        }

        public IDbDataParameter CreateParameter()
        {
            return m_cmd.CreateParameter();
        }

        public FbCommand DbCommand
        {
            get
            {
                return this.m_cmd;
            }
            set 
            { 
                this.m_cmd = value; 
            }
        }

        public DbParameterCollection Parameters
        {
            get
            {
                return m_cmd.Parameters;
            }
        }

        public string Sql
        {
            get
            {
                return m_cmd.CommandText;
            }
            set
            {
                m_cmd.CommandText = value;
            }
        }

        public string TableName
        {
            get
            {
                return m_cmd.CommandText;
            }
            set
            {
                m_cmd.CommandText = value;
            }
        }

        public string StoredProcedure
        {
            get
            {
                return m_cmd.CommandText;
            }
            set
            {
                m_cmd.CommandText = value;
            }
        }

        #region [==== Single command ====]
        public IDataReader ExecuteReader()
        {
            try
            {
                // Close Previous reader...
                if (m_reader != null && !m_reader.IsClosed)
                    m_reader.Close();

                // Open connection if it's closed
                if (m_cmd.Connection.State == ConnectionState.Closed)
                    m_cmd.Connection.Open();

                // Execute
                m_reader = m_cmd.ExecuteReader();
                return m_reader;
            }
            catch 
            {
                Close();
            }
            return null;
        }

        public IDataReader ExecuteReader(int row)
        {
            try
            {
                // Close Previous reader...
                if (m_reader != null && !m_reader.IsClosed)
                    m_reader.Close();

                // Open connection if it's closed
                if (m_cmd.Connection.State == ConnectionState.Closed)
                    m_cmd.Connection.Open();
                m_reader = m_cmd.ExecuteReader();
                while (row > 0)
                {
                    if (m_reader.Read())
                        row--;
                    else
                        break;
                }
                return m_reader;
            }
            catch 
            {
                Close();
            }
            return null;
        }

        public void ExecuteCommand()
        {
            using (m_cmd.Connection)
            {
                m_cmd.Connection.Open();
                m_cmd.ExecuteNonQuery();
            }
        }

        public object ExecuteScalar()
        {
            using (m_cmd.Connection)
            {
                if (m_cmd.Connection.State == ConnectionState.Closed)
                    m_cmd.Connection.Open();
                return m_cmd.ExecuteScalar();
            }
        }
        #endregion

        #region [==== Transaction ====]
        public void Begin()
        {
            Close();

            m_cmd.Connection.Open();
            m_cmd.Transaction = m_cmd.Connection.BeginTransaction();
        }

        public int ExecuteCommand(string sql)
        {
            // Close Previous reader...
            if (m_reader != null && !m_reader.IsClosed)
            {
                m_reader.Close();
                m_reader = null;
            }

            // Execute...
            m_cmd.CommandText = sql;
            if (m_cmd.Connection.State == ConnectionState.Closed)
                m_cmd.Connection.Open();
            return m_cmd.ExecuteNonQuery();
        }

        public IDataReader ExecuteReader(string sql)
        {
            m_cmd.CommandText = sql;

            // Close Previous reader...
            if (m_reader != null && !m_reader.IsClosed)
                m_reader.Close();

            // Open connection if one is closed
            if (m_cmd.Connection.State == ConnectionState.Closed)
                m_cmd.Connection.Open();

            // Save reader for closing in the next using time
            m_reader = m_cmd.ExecuteReader();
            return m_reader;
        }

        public void Commit()
        {
            m_cmd.Transaction.Commit();
        }

        public void RollBack()
        {
            try
            {
                m_cmd.Transaction.Rollback();
            }
            catch
            {
                throw;
            }
        }

        public void Close()
        {
            // Close Previous reader...
            if (m_reader != null && !m_reader.IsClosed)
                m_reader.Close();
            m_reader = null;

            // Close connection
            if (m_cmd.Connection.State != ConnectionState.Closed)
                m_cmd.Connection.Close();
        }

        #endregion

        #region Hỗ trợ truy vấn dữ liệu
        public DateTime? GetCurrentDateTime()
        {
            QueryBuilder query = new QueryBuilder(
                "select current_date as NgayCapNhat from rdb$database where 1=1");
            DataSet ds = this.LoadDataSet(query);
            if (ds != null) 
                return (DateTime?)Convert.ToDateTime(ds.Tables[0].Rows[0]["NgayCapNhat"]);
            return null;
        }

        public DataSet LoadDataSet(string storedProcedure)
        {
            return this.LoadDataSet(storedProcedure, null, null);
        }

        /// <summary>
        /// Lấy dữ liệu từ  storedProcedure có tham số truyền vào 
        /// </summary>
        public DataSet LoadDataSet(string storedProcedure, object[] ParameterNames, object[] ParameterValues)
        {
            DataSet ds = new DataSet();
            try
            {
                this.StoredProcedure = storedProcedure;
                this.m_cmd.CommandType = CommandType.StoredProcedure;
                if (ParameterNames != null && ParameterValues != null &&
                    ParameterNames.Length == ParameterValues.Length)
                {
                    for (int i = 0; i < ParameterNames.Length; i++)
                    {
                        this.m_cmd.Parameters.AddWithValue(ParameterNames[i].ToString(), ParameterValues[i]);
                    }
                }
                FbDataAdapter adap = new FbDataAdapter(this.m_cmd);
                adap.Fill(ds);
            }
            catch { 
                this.m_cmd.Connection.Close(); 
            }
            return ds;
        }

        /// <summary>
        /// Lấy dữ liệu từ chuỗi Sql được dựng từ QueryBuilder
        /// </summary>
        public bool LoadDataSet(DataSet ds, QueryBuilder query, string tableName)
        {
            if (ds == null) return false;
            if (tableName == null) tableName = "NO_NAME";
            try
            {
                m_cmd.CommandText = query.generateParamSQL();
                m_cmd.Parameters.AddRange(query.generateDbParam("PL").ToArray());
                LoadDataSet(ds, tableName);
                return true;
            }
            catch
            {
                return false;
            }
            finally { }
        }

        private void LoadDataSet(DataSet ds, string tableName)
        {
            try
            {
                FbDataAdapter adapter = new FbDataAdapter(m_cmd);
                adapter.Fill(ds, tableName);
            }
            catch { }
            finally 
            { 
                m_cmd.Connection.Close();
            }
        }

        public DataSet LoadReadOnlyDataSet(QueryBuilder query)
        {
            return LoadDataSet(query, null);
        }

        public DataSet LoadDataSet(QueryBuilder query, string tableName)
        {
            DataSet retDs = new DataSet();
            LoadDataSet(retDs, query, tableName);
            return retDs;
        }

        public DataSet LoadDataSet(QueryBuilder query)
        {
            return LoadDataSet(query, null);
        }

        public bool LoadDataSet(QueryBuilder query, string tableName, DataSet ds)
        {
            return LoadDataSet(ds, query, tableName);
        }
        #endregion

    }
}